package com.psedu.base.utils;

import cn.hutool.core.date.DateUtil;
import com.psedu.base.domain.vo.CertificateStudentVo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFPrintSetup;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class GraduateExcel {

    private final int onePageLength = 12;
    private final int centerSeparateIndex = 18;
    private final int rightLastIndex = 36;
    private final int pageLastRowIndex = onePageLength -1;

    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private int nowStudentIndex = 0;

    List<CertificateStudentVo> certificateStudentVoList;

    public GraduateExcel() {
        this.certificateStudentVoList = new ArrayList<>();
        initSheet();
    }

    public GraduateExcel(List<CertificateStudentVo> certificateStudentVos) {
        this.certificateStudentVoList = certificateStudentVos;
        initSheet();
    }

    public void initSheet() {
        wb = new HSSFWorkbook();
        sheet = wb.createSheet("结业证书");
        sheet.setDefaultRowHeightInPoints(40);
        // 设置打印页面为水平居中
        sheet.setHorizontallyCenter(true);
        // 设置打印页面为垂直居中
        sheet.setVerticallyCenter(true);
        HSSFPrintSetup printSetup = sheet.getPrintSetup();
        // 打印方向，true：横向，false：纵向
        printSetup.setLandscape(true);
        printSetup.setPaperSize(XSSFPrintSetup.A4_PAPERSIZE); //纸张大小，自选A4
    }

    public void allStudent2Sheet() {
        for (CertificateStudentVo certificateStudentVo : certificateStudentVoList) {
            nextStudent2Sheet();
        }
    }

    public void nextStudent2Sheet(){
        CertificateStudentVo certificateStudentVo = certificateStudentVoList.get(nowStudentIndex);
        for(int y = getNowStartRow(); y <= getNowLastRow(); y++) {
            HSSFRow row = sheet.createRow(y);
            for(int x = 0; x <= rightLastIndex; x++) {
                HSSFCellStyle defaultCellStyle = wb.createCellStyle();
                HSSFFont font = wb.createFont();
                font.setFontHeightInPoints((short) 12);
                font.setFontName("仿宋_GB2312");
                defaultCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
                defaultCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
                defaultCellStyle.setFont(font);
                HSSFCell cell = row.createCell(x);
                cell.setCellStyle(defaultCellStyle);
            }
        }

        allBorder();
        allMergeRegion();
        bottomHeight();

        for (int col = 0; col <= rightLastIndex; col++) {
            sheet.setColumnWidth(col, 850);
        }
        // left
        fillLeftContent();
        // center
        sheet.setColumnWidth(18, 1800);
        // right
        HSSFRow firstRow = sheet.getRow(getNowStartRow());
        HSSFCell nameLabelCell = firstRow.getCell(19);
        nameLabelCell.setCellValue("姓名");

        HSSFCell nameCell = firstRow.getCell(21);
        nameCell.setCellValue(certificateStudentVo.getStudentName());

        HSSFCell sexLabelCell = firstRow.getCell(25);
        sexLabelCell.setCellValue("性别");

        HSSFCell sexCell = firstRow.getCell(27);
        sexCell.setCellValue(certificateStudentVo.getSex());

        HSSFCell birthdayLabelCell = firstRow.getCell(29);
        birthdayLabelCell.setCellValue("出生日期");
        HSSFCellStyle birthdayLabelCellStyle = getHssfCellStyle(birthdayLabelCell);
        birthdayLabelCellStyle.setWrapText(true);


        HSSFCell birthdayCell = firstRow.getCell(31);
        birthdayCell.setCellValue(DateUtil.format(certificateStudentVo.getBirthday(), "yyyy.MM.dd"));

        HSSFCellStyle birthdayCellStyle = getHssfCellStyle(birthdayCell);
        HSSFFont font = getHssfFont(birthdayCellStyle);
        font.setFontHeightInPoints((short) 9);
        birthdayCellStyle.setWrapText(true);

        HSSFCell profileCell = firstRow.getCell(33);
        profileCell.setCellValue("头像");

        HSSFRow secondRow = sheet.getRow(getNowStartRow()+ 1);
        HSSFCell unitLabelCell = secondRow.getCell(19);
        unitLabelCell.setCellValue("单位");
        HSSFCell unitCell = secondRow.getCell(21);
        unitCell.setCellValue(certificateStudentVo.getUnit());

        HSSFRow thirdRow = sheet.getRow(getNowStartRow()+ 2);
        HSSFCell trainCaseLabelCell = thirdRow.getCell(19);
        trainCaseLabelCell.setCellValue("培 训 情 况");

        HSSFRow fourthRow = sheet.getRow(getNowStartRow()+ 3);
        HSSFCell trainTimeLabelCell = fourthRow.getCell(19);
        trainTimeLabelCell.setCellValue("培训时间");
        HSSFCell scoreLabelCell = fourthRow.getCell(28);
        scoreLabelCell.setCellValue("考核（考试）成绩");

        HSSFRow fifthRow = sheet.getRow(getNowStartRow()+ 4);
        HSSFCell trainTimeCell = fifthRow.getCell(19);
        trainTimeCell.setCellValue("2022.04-2022.04");
        String startTime = DateUtil.format(certificateStudentVo.getTrainStartDate(), "yyyy.MM");
        String endTime = DateUtil.format(certificateStudentVo.getTrainEndDate(), "yyyy.MM");
        trainTimeCell.setCellValue(
                startTime +"-" +endTime
        );
        HSSFCell scoreCell = fifthRow.getCell(28);
        scoreCell.setCellValue(certificateStudentVo.getScore().toString());

        HSSFRow sixthRow = sheet.getRow(getNowStartRow()+ 5);
        HSSFCell evaluateCell = sixthRow.getCell(19);
        evaluateCell.setCellValue(certificateStudentVo.getComment());
        HSSFCellStyle evaluateCellStyle = getHssfCellStyle(evaluateCell);
        HSSFFont evaluateFont = getHssfFont(evaluateCellStyle);
        evaluateFont.setFontHeightInPoints((short) 14);
        evaluateCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
        evaluateCellStyle.setWrapText(true);

        HSSFRow tenthRow = sheet.getRow(getNowStartRow()+ 9);
        HSSFCell cell1 = tenthRow.getCell(27);
        cell1.setCellValue(certificateStudentVo.getIssueOrganization());
        HSSFRow eleventhRow = sheet.getRow(getNowStartRow()+ 10);
        HSSFCell cell2 = eleventhRow.getCell(27);
        cell2.setCellValue(DateUtil.format( certificateStudentVo.getIssueDate(), "yyyy年MM月dd日"));

        cellInsertImage(getNowStartRow(), certificateStudentVo.getProfile());
        sheet.setRowBreak(getNowLastRow());
        nowStudentIndex++;
    }

    private int getNowStartRow() {
        return this.nowStudentIndex * this.onePageLength;
    }

    private int getNowLastRow() {
        return ( this.nowStudentIndex+ 1) * this.onePageLength -1;
    }

    public void downloadExcel2Local() throws IOException {
        this.allStudent2Sheet();
        FileOutputStream fileOut = new FileOutputStream("D:\\code\\school-project\\file\\test.xls");
        wb.write(fileOut);
    }

    public HSSFWorkbook getResWorkbook() {
        this.allStudent2Sheet();
        return this.wb;
    }


    private HSSFFont getHssfFont( HSSFCellStyle cellStyle) {
        HSSFFont font = cellStyle.getFont(wb);
        if(font == null) {
            font = wb.createFont();
        }
        return font;
    }

    private void allMergeRegion() {
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow(), getNowStartRow(), 19, 20));
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow(), getNowStartRow(), 21, 24));
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow(), getNowStartRow(), 25, 26));
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow(), getNowStartRow(), 27, 28));
        // 出生日期 label
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow(), getNowStartRow(), 29, 30));
        // 出生日期
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow(), getNowStartRow(), 31, 32));
        // 头像
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow(), getNowStartRow() +1, 33, 36));

        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +1, getNowStartRow() +1, 19, 20));
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +1, getNowStartRow() +1, 21, 32));

        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +2, getNowStartRow() +2, 19, 36));

        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +3, getNowStartRow() +3, 19, 27));
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +3, getNowStartRow() +3, 28, 36));
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +4, getNowStartRow() +4, 19, 27));
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +4, getNowStartRow() +4, 28, 36));

        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +5, getNowStartRow() +8, 19, 36));

        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +9, getNowStartRow() +9, 27, 36));
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +10, getNowStartRow() +10, 27, 36));


    }

    private void bottomHeight() {
        for(int rowIndex = 8; rowIndex <= pageLastRowIndex; rowIndex++) {
            HSSFRow row = sheet.getRow(getNowStartRow()+ rowIndex);
            row.setHeight((short) 400);
        }

    }

    private void allBorder() {
        // 第一行
        HSSFRow firstRow = sheet.getRow(getNowStartRow());
        for(int i = 0; i <= rightLastIndex; i++) {
            HSSFCell cell = firstRow.getCell(i);
            HSSFCellStyle cellStyle = getHssfCellStyle(cell);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
            cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
            cell.setCellStyle(cellStyle);
        }
        // 第一列
        for(int i = 0; i <= pageLastRowIndex; i++) {
            HSSFRow nowRow = sheet.getRow(getNowStartRow()+ i);
            HSSFCell cell = nowRow.getCell(0);
            HSSFCellStyle cellStyle = getHssfCellStyle(cell);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            cell.setCellStyle(cellStyle);
        }
        // 最后一行
        HSSFRow lastRow = sheet.getRow(getNowLastRow());
        for(int i = 0; i <= rightLastIndex; i++) {
            HSSFCell cell = lastRow.getCell(i);
            HSSFCellStyle cellStyle = getHssfCellStyle(cell);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
            cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
            cell.setCellStyle(cellStyle);
        }
        // 最后一列
        for(int i = 0; i <= pageLastRowIndex; i++) {
            HSSFRow nowRow = sheet.getRow(getNowStartRow()+ i);
            HSSFCell cell = nowRow.getCell(rightLastIndex);
            HSSFCellStyle cellStyle = getHssfCellStyle(cell);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            cell.setCellStyle(cellStyle);
        }
        // 中间分隔
        for(int i = 0; i <= pageLastRowIndex; i++) {
            HSSFRow nowRow = sheet.getRow(getNowStartRow()+ i);
            HSSFCell cell = nowRow.getCell(centerSeparateIndex);
            HSSFCellStyle cellStyle = getHssfCellStyle(cell);
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
            cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
            cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
            cellStyle.setBorderTop(HSSFCellStyle.BORDER_NONE);
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_NONE);
            cell.setCellStyle(cellStyle);
        }
        // 右上侧边框
        for(int rowIndex = 0; rowIndex <= 7; rowIndex++) {
            HSSFRow nowRow = sheet.getRow(getNowStartRow()+ rowIndex);
            for(int colIndex = centerSeparateIndex +1; colIndex <= rightLastIndex; colIndex++) {
                HSSFCell cell = nowRow.getCell(colIndex);
                HSSFCellStyle cellStyle = getHssfCellStyle(cell);
                cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
                cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
                cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
                cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
                cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
                cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
                cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
                cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
                cell.setCellStyle(cellStyle);
            }
        }
    }

    private HSSFCellStyle getHssfCellStyle(HSSFCell cell) {
        HSSFCellStyle cellStyle = cell.getCellStyle();
        if (cellStyle == null) {
            cellStyle = wb.createCellStyle();
        }
        return cellStyle;
    }

    private void fillLeftContent() {
        HSSFRow row1 = sheet.getRow(getNowStartRow()+ 1);
        HSSFCell oathTitleCell = row1.getCell(0);
        HSSFCellStyle oathCellStyle = oathTitleCell.getCellStyle();
        oathCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        oathTitleCell.setCellValue("入党誓词");
        HSSFFont oathCellFont = getHssfFont(oathCellStyle);
        oathCellFont.setFontHeightInPoints((short) 24);
        oathCellStyle.setFont(oathCellFont);
        oathTitleCell.setCellStyle(oathCellStyle);
        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +1, getNowStartRow() +1, 0, 17));

        HSSFRow row2 = sheet.getRow(getNowStartRow()+ 2);
        HSSFCell oathCell = row2.getCell(3);
        HSSFCellStyle cellStyle = oathCell.getCellStyle();
        cellStyle.setWrapText(true);
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_JUSTIFY);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFFont font = getHssfFont(cellStyle);
        font.setFontHeightInPoints((short) 20);
        cellStyle.setFont(font);
        oathCell.setCellValue("　 我志愿加入中国共产党，拥护党的纲领，遵守党的章程，履行党员义务，执行党的决定，严守党的纪律，保守党的秘密，对党忠诚，积极工作，为共产主义奋斗终身，随时准备为党和人民牺牲一切，永不叛党。");

        sheet.addMergedRegion(new CellRangeAddress(getNowStartRow() +2, getNowStartRow() +9, 3, 14));
    }


    //Excel单元格插入图片
    public void cellInsertImage(int nowRow, String imageUrl)  {
        if(imageUrl == null || imageUrl.equals("")) {
            return;
        }
        BufferedImage bufferImg = null;
        try {
            File imageFile = new File(imageUrl);
            InputStream ins;
            byte[] picDatas = null;
            try {
                ins = new FileInputStream(imageFile);
                picDatas = new byte[ins.available()];
                ins.read(picDatas);
                ins.close();
            } catch (Exception e) {
                e.printStackTrace();
            }

            ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
            //将图片读到BufferedImage
            bufferImg = ImageIO.read(imageFile);
            // 将图片写入流中
            ImageIO.write(bufferImg, "png", byteArrayOut);
            // 利用HSSFPatriarch将图片写入EXCEL
            Drawing patriarch = sheet.createDrawingPatriarch();
            /**
             * 该构造函数有8个参数
             * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
             * 后四个参数，前连个表示图片左上角所在的cellNum和 rowNum，后天个参数对应的表示图片右下角所在的cellNum和 rowNum，
             * excel中的cellNum和rowNum的index都是从0开始的
             */
            //图片导出
            HSSFClientAnchor anchor = new HSSFClientAnchor(
                    0, 0, 0, 0,
                    (short) 33, nowRow, (short)37, nowRow +2
            );
            anchor.setDx1(300);
            anchor.setDy1(10);
            anchor.setDx2(-100);
            anchor.setDy2(-10);
            // 插入图片
            Picture picture = patriarch.createPicture(
                    anchor,
                    wb.addPicture(byteArrayOut.toByteArray(),
                            HSSFWorkbook.PICTURE_TYPE_PNG
                    )
            );
//            picture.resize(0.3);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

}
